How to find blocked processes

Comments 0

Share to social media

Blocked processes are often a big problem to DBA’s. They are difficult to monitor. We receive desperate calls saying “Everything is slow!” and before we can do anything a second call “Oh, don’t worry, it’s everything ok again”.

Most of times it happens because ill-behaved process that blocks other tasks for short periods. How can we find this kind of problem?

SQL Server has a server configuration called Blocked Process Threshold. We can set a value, in milliseconds, and SQL Server will generate a report every time a process is blocked  by this amount of time.

To configure ‘Blocked Process Threshold’ we can use the following code:

 In this example one Blocked Process report will be generated every time one process is blocked for more the five seconds. You need to adapt this value to your servers.

There are a few ways to capture blocked process report:

  • SQL Profiler: SQL Profiler has an event called ‘Blocked Process Report’ especially to capture this information.
  • Extended Events: XE also has a ‘Blocked Process Report’ event.
  • Alerts: It’s possible to generate an alert over blocked process reports and generate an email as alert response.

You can create an extended events session to capture the blocked process report:

After creating and starting the session, we can query the reports captured by this session. We need to use the DMF (Dynamic Management Function) sys.fn_xe_file_target_read_file to read the session information, but the main information is returned as XML field, so we will need to extract the information from the XML using XML functions.

The query to retrieve the report will be this:

Notice that extended events hasn’t a solution to notify about the reports. You can achieve a blocked process notification solution using an alert.

SQL Server has a performance counter called Process Blocked that counts the number of blocked process according to ‘Blocked Process Threshold’ configuration. We can create an alert over this counter and configure the notification to send an e-mail to the operator.

The script to create the alert will be this:

— Add one e-mail notification to one operator

Redgate SQL Monitor also has an alert called ‘Blocked Process’ with some differences:

  • It doesn’t requires the ‘Blocked Process Threshold’ to be configured on the server
  • The information is retrieved by the server that holds SQL Monitor, from several different SQL Servers
  • We can do one single configuration for the alert to check several servers
  • It has a notification solution, so you can receive notifications by e-mail.

Load comments

About the author

Dennes Torres

See Profile

Dennes Torres is a Data Platform MVP and Software Architect living in Malta who loves SQL Server and software development and has more than 20 years of experience. Dennes can improve Data Platform Architectures and transform data in knowledge. He moved to Malta after more than 10 years leading devSQL PASS Chapter in Rio de Janeiro and now is a member of the leadership team of MMDPUG PASS Chapter in Malta organizing meetings, events, and webcasts about SQL Server. He is an MCT, MCSE in Data Platforms and BI, with more titles in software development. You can get in touch on his blog https://dennestorres.com or at his work https://dtowersoftware.com